﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using System.Data;
using DTO;
using System.Data.Sql;

namespace DAO
{
    public class SachDAO
    {
        private DataProvider provider;

        public SachDAO()
        {
            provider = new DataProvider();
        }
        
        public int ThemSach(SachDTO sach)
        {

            string sqlStatement = "INSERT INTO Sach(TenSach, MaLoaiSach, SoLuongTon, GiaBan, TacGia, FileHinh, DaXoa)"
                + " VALUES (N'" + sach.TenSach + "', " + sach.MaLoaiSach + ", " + sach.SoLuongTon 
                + ", " + sach.GiaBan + ", N'" + sach.TacGia + "', N'" + sach.FileHinh + "', 0)";

            return provider.ExecuteNonQuery(sqlStatement);
        }

        public int CapNhatSach(SachDTO sach)
        {
            string sqlStatement = "UPDATE Sach SET TenSach=N'" + sach.TenSach + "', MaLoaiSach=" + sach.MaLoaiSach 
                + ", SoLuongTon=" + sach.SoLuongTon + ", GiaBan=" + sach.GiaBan + ", TacGia=N'" + sach.TacGia 
                + "', FileHinh=N'" + sach.FileHinh + "' WHERE MaSach=" + sach.MaSach;
            
            return provider.ExecuteNonQuery(sqlStatement);
        }

        public int XoaSach(SachDTO sach)
        {

            string sqlStatement = "UPDATE Sach SET DaXoa=1 WHERE MaSach=" + sach.MaSach;

            return provider.ExecuteNonQuery(sqlStatement);
        }

        public ArrayList LayDanhSachSach()
        {
            ArrayList arrSach = new ArrayList();
            try
            {
                string query = "SELECT * FROM Sach WHERE DaXoa=0 ORDER BY GiaBan ASC";
                DataTable dt = new DataTable();
                dt = provider.ExecuteQuery(query);

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    SachDTO item = new SachDTO();

                    item.MaSach = int.Parse(dt.Rows[i].ItemArray[0].ToString());
                    item.TenSach = dt.Rows[i].ItemArray[1].ToString();
                    item.MaLoaiSach = int.Parse(dt.Rows[i].ItemArray[2].ToString());
                    item.SoLuongTon = int.Parse(dt.Rows[i].ItemArray[3].ToString());
                    item.GiaBan = float.Parse(dt.Rows[i].ItemArray[4].ToString());
                    item.TacGia = dt.Rows[i].ItemArray[5].ToString();
                    item.FileHinh = dt.Rows[i].ItemArray[6].ToString();
                    item.DaXoa = bool.Parse(dt.Rows[i].ItemArray[7].ToString());

                    arrSach.Add(item);
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Error: " + ex.Message);
            }
            return arrSach;
        }

        public SachDTO LaySachTheoMaSach(int maSach)
        {
            try
            {
                string sql = "SELECT * FROM Sach WHERE MaSach=" + maSach + " AND DaXoa=0";
                DataTable dt = provider.ExecuteQuery(sql);

                SachDTO sach = null;
                if (dt.Rows.Count > 0)
                {
                    sach = new SachDTO();
                    sach.MaSach = int.Parse(dt.Rows[0].ItemArray[0].ToString());
                    sach.TenSach = dt.Rows[0].ItemArray[1].ToString();
                    sach.MaLoaiSach = int.Parse(dt.Rows[0].ItemArray[2].ToString());
                    sach.SoLuongTon = int.Parse(dt.Rows[0].ItemArray[3].ToString());
                    sach.GiaBan = float.Parse(dt.Rows[0].ItemArray[4].ToString());
                    sach.TacGia = dt.Rows[0].ItemArray[5].ToString();
                    sach.FileHinh = dt.Rows[0].ItemArray[6].ToString();
                }

                return sach;
            }
            catch (Exception)
            {
                return null;
            }
        }

        public ArrayList LayDanhSachSachTheoTenTacGia(string tenTacGia)
        {
            ArrayList arrSach = new ArrayList();
            try
            {
                string query = "SELECT * FROM Sach WHERE TacGia=" + tenTacGia + " AND DaXoa=0 ORDER BY GiaBan ASC";
                DataTable dt = new DataTable();
                dt = provider.ExecuteQuery(query);

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    SachDTO item = new SachDTO();

                    item.MaSach = int.Parse(dt.Rows[i].ItemArray[0].ToString());
                    item.TenSach = dt.Rows[i].ItemArray[1].ToString();
                    item.MaLoaiSach = int.Parse(dt.Rows[i].ItemArray[2].ToString());
                    item.SoLuongTon = int.Parse(dt.Rows[i].ItemArray[3].ToString());
                    item.GiaBan = float.Parse(dt.Rows[i].ItemArray[4].ToString());
                    item.TacGia = dt.Rows[i].ItemArray[5].ToString();
                    item.FileHinh = dt.Rows[i].ItemArray[6].ToString();

                    arrSach.Add(item);
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Error: " + ex.Message);
            }
            return arrSach;
        }

        public ArrayList LayDanhSachSachTheoMaLoaiSach(string maLoaiSach)
        {
            ArrayList arrSach = new ArrayList();
            try
            {
                string query = "SELECT * FROM Sach WHERE MaLoaiSach=" + maLoaiSach + " AND DaXoa=0 ORDER BY GiaBan ASC";
                DataTable dt = new DataTable();
                dt = provider.ExecuteQuery(query);

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    SachDTO item = new SachDTO();

                    item.MaSach = int.Parse(dt.Rows[i].ItemArray[0].ToString());
                    item.TenSach = dt.Rows[i].ItemArray[1].ToString();
                    item.MaLoaiSach = int.Parse(dt.Rows[i].ItemArray[2].ToString());
                    item.SoLuongTon = int.Parse(dt.Rows[i].ItemArray[3].ToString());
                    item.GiaBan = float.Parse(dt.Rows[i].ItemArray[4].ToString());
                    item.TacGia = dt.Rows[i].ItemArray[5].ToString();
                    item.FileHinh = dt.Rows[i].ItemArray[6].ToString();

                    arrSach.Add(item);
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Error: " + ex.Message);
            }
            return arrSach;
        }

        public ArrayList TimKiemSach(string query)
        {
            ArrayList arrSach = new ArrayList();
            try
            {
                DataTable dt = new DataTable();
                dt = provider.ExecuteQuery(query);

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    SachDTO item = new SachDTO();

                    item.MaSach = int.Parse(dt.Rows[i].ItemArray[0].ToString());
                    item.TenSach = dt.Rows[i].ItemArray[1].ToString();
                    item.MaLoaiSach = int.Parse(dt.Rows[i].ItemArray[2].ToString());
                    item.SoLuongTon = int.Parse(dt.Rows[i].ItemArray[3].ToString());
                    item.GiaBan = float.Parse(dt.Rows[i].ItemArray[4].ToString());
                    item.TacGia = dt.Rows[i].ItemArray[5].ToString();
                    item.FileHinh = dt.Rows[i].ItemArray[6].ToString();

                    arrSach.Add(item);
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Error: " + ex.Message);
            }
            return arrSach;
        }

        public ArrayList TimKiemSachTheoTenSach(string keyword)
        {
            string query = "SELECT * FROM Sach WHERE TenSach LIKE '%" + keyword + "%' AND DaXoa=0";

            return TimKiemSach(query);
        }

        public ArrayList TimKiemSachTheoTenTacGia(string keyword)
        {
            string query = "SELECT * FROM Sach WHERE TacGia LIKE '%" + keyword + "%' AND DaXoa=0";

            return TimKiemSach(query);
        }

        public ArrayList TimKiemSachTheoTenLoaiSach(string keyword)
        {
            string query = "SELECT s.MaSach, s.TenSach, s.MaLoaiSach, s.SoLuongTon, s.GiaBan, s.TacGia, s.FileHinh, s.DaXoa"
                + " FROM Sach s, LoaiSach ls WHERE s.MaLoaiSach=ls.MaLoaiSach"
                + " AND ls.TenLoaiSach LIKE '%" + keyword + "%' AND DaXoa=0";

            return TimKiemSach(query);
        }
    }
}
